﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using DotNetNuke;
using DotNetNuke.Common.Utilities;
using DotNetNuke.Entities.Modules;
using DotNetNuke.Services.Search;
using DotNetNuke.Security;
using System.Data;
using System.Collections;

namespace DNNToolkit.Modules.CityTaxLocator.Components
{
    public class CityTaxLocatorController : IPortable, ISearchable
    {

        #region Constructors

        public CityTaxLocatorController()
        {

        }

        #endregion

        #region Public Methods

        /// <summary>
        /// Searches the city tax.
        /// when all the params are Null or Empty, Get All the city tax.
        /// </summary>
        /// <param name="state">The state value.</param>
        /// <param name="city">The city value.</param>
        /// <param name="code">The code value.</param>
        /// <returns></returns>
        public List<CityTaxInfo> SearchCityTax(string state, string city, string code)
        {
            city += "%";

            List<CityTaxInfo> list = new List<CityTaxInfo>();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                if (!string.IsNullOrEmpty(code.Trim()))
                {
                    list = CollectCityTaxInfo(provider.SearchCityTaxByCode(code.Trim()));
                }
                else if (string.IsNullOrEmpty((state + city + code).Trim().Replace("%", string.Empty)))
                {
                    //when all the params are Null or Empty, Get All the city tax
                    list = CollectCityTaxInfo(provider.GetAllCityTax());
                }
                else
                {
                    list = CollectCityTaxInfo(provider.SearchCityTaxByCity(state, city));
                }
            }
            finally
            {
                provider.CloseConnection();
            }
            return list;
        }

        /// <summary>
        /// Gets the city tax by ID.
        /// when there is no such record, return null.
        /// </summary>
        /// <param name="cityTaxID">The city tax ID.</param>
        /// <returns></returns>
        public CityTaxInfo GetCityTaxByID(long cityTaxID)
        {
            List<CityTaxInfo> cityTaxInfo = new List<CityTaxInfo>();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                cityTaxInfo = CollectCityTaxInfo(provider.GetCityTaxByID(cityTaxID));
                // the count should be only 1 or 0
                if (cityTaxInfo.Count == 1)
                {
                    return cityTaxInfo[0];
                }
            }
            finally
            {
                provider.CloseConnection();
            }
            // when there is no such record, return null
            return null;
        }

        /// <summary>
        /// Gets the ALL city tax.
        /// </summary>
        /// <returns></returns>
        public List<CityTaxInfo> GetALLCityTax()
        {
            List<CityTaxInfo> cityTaxInfo = new List<CityTaxInfo>();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                cityTaxInfo = CollectCityTaxInfo(provider.GetAllCityTax());
                return cityTaxInfo;
            }
            finally
            {
                provider.CloseConnection();
            }
            // when there is no such record, return null
        }

        public List<string> SearchStates()
        {
            List<string> list = new List<string>();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                list = CollectStates(provider.SearchCityTaxStates());
            }
            finally
            {
                provider.CloseConnection();
            }
            return list;
        }

        public CityTaxCommentInfo GetCityTaxComment()
        {
            CityTaxCommentInfo objComment = null;
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                List<CityTaxCommentInfo> list = CollectCityTaxComments(provider.SearchCityTaxComment());
                if (list.Count > 0)
                    objComment = list[0];
            }
            finally
            {
                provider.CloseConnection();
            }
            return objComment;
        }

        /// <summary>
        /// Get the city tax record count.
        /// </summary>
        /// <returns></returns>
        public int GetCityTaxRecordCount()
        {
            int count = 0;
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                count = provider.SearchCityTaxCount();
            }
            finally
            {
                provider.CloseConnection();
            }
            return count;
        }

        /// <summary>
        /// Delete the city tax.
        /// </summary>
        public void DeleteCityTax()
        {
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            try
            {
                provider.DeleteCityTax();
            }
            finally
            {
                provider.CloseConnection();
            }
        }

        /// <summary>
        /// Deletes the city tax by ID.
        /// </summary>
        /// <param name="cityTaxID">The city tax ID.</param>
        public void DeleteCityTaxByID(long cityTaxID)
        {
            bool bSuccess = false;
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            provider.BeginTrans();
            try
            {
                provider.DeleteCityTaxByID(cityTaxID);
                provider.Commit();
                bSuccess = true;
            }
            finally
            {
                if (!bSuccess)
                    provider.RollBack();
                provider.CloseConnection();
            }
        }

        /// <summary>
        /// Updates the city tax info.
        /// </summary>
        /// <param name="cityTaxInfo">The city tax info.</param>
        public void UpdateCityTaxInfo(CityTaxInfo cityTaxInfo)
        {
            bool bSuccess = false;
            CityTaxCommentInfo comment = GetCityTaxComment();
            if (comment == null)
                comment = new CityTaxCommentInfo();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            // begin transition
            provider.BeginTrans();
            try
            {
                // update record
                provider.UpdateCityTaxById(cityTaxInfo.CityTaxID, cityTaxInfo.State, cityTaxInfo.LocalName, cityTaxInfo.LocRate,
                    cityTaxInfo.SchlRate, cityTaxInfo.WhRate, cityTaxInfo.W2Name, cityTaxInfo.LocalCD,
                    cityTaxInfo.ResInd, cityTaxInfo.CountyName, cityTaxInfo.AdditionalJur, cityTaxInfo.Comments);

                // update CityTaxComment
                provider.DeleteCityTaxComment();
                comment.LastUpdate = DateTime.Now;
                provider.CreateCityTaxComment(comment.LastUpdate, comment.AdditionComment);
                bSuccess = true;
                provider.Commit();
            }
            finally
            {
                if (!bSuccess)
                    provider.RollBack();
                provider.CloseConnection();
            }
        }

        /// <summary>
        /// Update the city tax info.
        /// </summary>
        /// <param name="stream">The stream.</param>
        public void UpdateCityTaxInfo(System.IO.FileStream stream)
        {
            bool bSuccess = false;
            CityTaxCommentInfo comment = GetCityTaxComment();
            if (comment == null)
                comment = new CityTaxCommentInfo();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            provider.BeginTrans();
            try
            {
                List<CityItem> list = AnaysisData(stream);
                // delete old data
                provider.DeleteCityTax();

                // insert new data
                if (list != null && list.Count > 0)
                {
                    foreach (CityItem cityItem in list)
                    {
                        CityTaxInfo objCityTax = ConvertItem(cityItem);
                        provider.CreateCityTax(objCityTax.State, objCityTax.LocalName, objCityTax.LocRate
                            , objCityTax.SchlRate, objCityTax.WhRate, objCityTax.W2Name, objCityTax.LocalCD
                            , objCityTax.ResInd, objCityTax.CountyName, objCityTax.AdditionalJur, objCityTax.Comments);
                    }
                }
                // update CityTaxComment
                provider.DeleteCityTaxComment();
                comment.LastUpdate = DateTime.Now;
                provider.CreateCityTaxComment(comment.LastUpdate, comment.AdditionComment);
                bSuccess = true;
                provider.Commit();
            }
            finally
            {
                if (!bSuccess)
                    provider.RollBack();
                provider.CloseConnection();
            }
        }

        /// <summary>
        /// Insert citytax data.
        /// </summary>
        /// <param name="cityTaxInfos">The city tax infos.</param>
        public void InsertCityTaxInfo(List<CityTaxInfo> cityTaxInfos)
        {
            bool bSuccess = false;
            CityTaxCommentInfo comment = GetCityTaxComment();
            if (comment == null)
                comment = new CityTaxCommentInfo();
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            // start a Transtion
            provider.BeginTrans();
            try
            {
                // delete old data
                provider.DeleteCityTax();

                // insert new data
                foreach (CityTaxInfo citytax in cityTaxInfos)
                {
                    provider.CreateCityTax(citytax.State, citytax.LocalName, citytax.LocRate
                        , citytax.SchlRate, citytax.WhRate, citytax.W2Name, citytax.LocalCD
                        , citytax.ResInd, citytax.CountyName, citytax.AdditionalJur, citytax.Comments);
                }

                // update Comment
                provider.DeleteCityTaxComment();
                comment.LastUpdate = DateTime.Now;
                provider.CreateCityTaxComment(comment.LastUpdate, comment.AdditionComment);
                bSuccess = true;
                // commit
                provider.Commit();
            }
            finally
            {
                if (!bSuccess)
                    provider.RollBack(); // rollback
                provider.CloseConnection();
            }
        }

        /// <summary>
        /// Updates the city tax comment info.
        /// </summary>
        /// <param name="comment">The comment.</param>
        public void UpdateCityTaxCommentInfo(CityTaxCommentInfo comment)
        {
            bool bSuccess = false;
            DataProvider provider = DataProvider.Instance();
            provider.OpenConnection();
            provider.BeginTrans();
            try
            {
                provider.DeleteCityTaxComment();
                provider.CreateCityTaxComment(comment.LastUpdate, comment.AdditionComment);
                bSuccess = true;
                provider.Commit();
            }
            finally
            {
                if (!bSuccess)
                    provider.RollBack();
                provider.CloseConnection();
            }
        }

        private CityTaxInfo ConvertItem(CityItem cityItem)
        {
            CityTaxInfo objCityTax = new CityTaxInfo();
            objCityTax.AdditionalJur = string.IsNullOrEmpty(cityItem.AdditionalJur) ? string.Empty : cityItem.AdditionalJur.Trim();
            objCityTax.Comments = string.IsNullOrEmpty(cityItem.Comments) ? string.Empty : cityItem.Comments.Trim();
            objCityTax.CountyName = string.IsNullOrEmpty(cityItem.CountyName) ? string.Empty : cityItem.CountyName.Trim();
            objCityTax.LocalCD = string.IsNullOrEmpty(cityItem.LocalCD) ? string.Empty : cityItem.LocalCD.Trim();
            objCityTax.LocalName = string.IsNullOrEmpty(cityItem.LocalName) ? string.Empty : cityItem.LocalName.Trim();
            objCityTax.LocRate = string.IsNullOrEmpty(cityItem.LocRate) ? string.Empty : cityItem.LocRate.Trim();
            objCityTax.ResInd = string.IsNullOrEmpty(cityItem.ResInd) ? string.Empty : cityItem.ResInd.Trim();
            objCityTax.SchlRate = string.IsNullOrEmpty(cityItem.SchlRate) ? string.Empty : cityItem.SchlRate.Trim();
            objCityTax.State = string.IsNullOrEmpty(cityItem.State) ? string.Empty : cityItem.State.Trim();
            objCityTax.W2Name = string.IsNullOrEmpty(cityItem.W2Name) ? string.Empty : cityItem.W2Name.Trim();
            objCityTax.WhRate = string.IsNullOrEmpty(cityItem.WhRate) ? string.Empty : cityItem.WhRate.Trim();
            return objCityTax;
        }

        /// <summary>
        /// Anaysises the data.
        /// </summary>
        /// <param name="stream">The stream.</param>
        /// <returns></returns>
        private List<CityItem> AnaysisData(System.IO.FileStream stream)
        {
            List<CityItem> list = new List<CityItem>();
            StateBlockManager stateMgr = new StateBlockManager(stream);
            foreach (StateItem state in stateMgr.States())
            {
                foreach (CityItem city in state.Items())
                {
                    list.Add(city);
                }
            }
            return list;
        }

        /// <summary>
        /// Collects the city tax comments.
        /// </summary>
        /// <param name="dataReader">The data reader.</param>
        /// <returns></returns>
        private List<CityTaxCommentInfo> CollectCityTaxComments(IDataReader dataReader)
        {
            List<CityTaxCommentInfo> list = new List<CityTaxCommentInfo>();
            try
            {
                while (dataReader.Read())
                {
                    CityTaxCommentInfo objComment = new CityTaxCommentInfo();
                    objComment.AdditionComment = dataReader["AdditionComment"].ToString().Trim();
                    objComment.LastUpdate = Convert.ToDateTime(dataReader["LastUpdate"]);
                    list.Add(objComment);
                }
            }
            finally
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// Collects the states.
        /// </summary>
        /// <param name="dataReader">The data reader.</param>
        /// <returns></returns>
        private List<string> CollectStates(IDataReader dataReader)
        {
            List<string> list = new List<string>();
            try
            {
                while (dataReader.Read())
                {
                    list.Add(dataReader["State"].ToString().Trim());
                }
            }
            finally
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// Collects the city tax info.
        /// </summary>
        /// <param name="dataReader">The data reader.</param>
        /// <returns></returns>
        private List<CityTaxInfo> CollectCityTaxInfo(IDataReader dataReader)
        {
            List<CityTaxInfo> list = new List<CityTaxInfo>();
            try
            {
                while (dataReader.Read())
                {
                    CityTaxInfo objCityTax = new CityTaxInfo();
                    objCityTax.CityTaxID = Convert.ToInt64(dataReader["CityTaxID"]);
                    objCityTax.AdditionalJur = dataReader["AdditionalJur"].ToString().Trim();
                    objCityTax.Comments = dataReader["Comments"].ToString().Trim();
                    objCityTax.CountyName = dataReader["CountyName"].ToString().Trim();
                    objCityTax.LocalCD = dataReader["LocalCD"].ToString().Trim();
                    objCityTax.LocalName = dataReader["LocalName"].ToString().Trim();
                    objCityTax.LocRate = dataReader["LocRate"].ToString().Trim();
                    objCityTax.ResInd = dataReader["ResInd"].ToString().Trim();
                    objCityTax.SchlRate = dataReader["SchlRate"].ToString().Trim();
                    objCityTax.State = dataReader["State"].ToString().Trim();
                    objCityTax.W2Name = dataReader["W2Name"].ToString().Trim();
                    objCityTax.WhRate = dataReader["WhRate"].ToString().Trim();
                    list.Add(objCityTax);
                }
            }
            finally
            {
                if (!dataReader.IsClosed)
                {
                    dataReader.Close();
                    dataReader.Dispose();
                }
            }
            return list;
        }

        #endregion

        #region Optional Interfaces

        #endregion

        #region IPortable Members

        public string ExportModule(int ModuleID)
        {
            List<CityTaxInfo> list = SearchCityTax(string.Empty, string.Empty, string.Empty);

            XmlDocument xml = new XmlDocument();

            XmlNode root = xml.CreateElement(CityTaxInfoConstant.XmlRootName);

            List<XmlNode> propertyNodes;

            // create a string[] for the property node names
            string[] propertys = { CityTaxFieldName.State.ToString(),
                                     CityTaxFieldName.LocalName.ToString(),
                                     CityTaxFieldName.LocRate.ToString(),
                                     CityTaxFieldName.SchlRate.ToString(),
                                     CityTaxFieldName.WhRate.ToString(),
                                     CityTaxFieldName.W2Name.ToString(),
                                     CityTaxFieldName.LocalCD.ToString(),
                                     CityTaxFieldName.ResInd.ToString(),
                                     CityTaxFieldName.CountyName.ToString(),
                                     CityTaxFieldName.AdditionalJur.ToString(),
                                     CityTaxFieldName.Comments.ToString()};

            // Export Data to XML nodes
            foreach (CityTaxInfo citytaxinfo in list)
            {
                XmlNode cityTaxsNode = xml.CreateElement(CityTaxInfoConstant.XmlNodeName);
                propertyNodes = new List<XmlNode>();
                for (int i = 0; i < propertys.Length; i++)
                {
                    XmlNode propertyNode = xml.CreateElement(propertys[i]);
                    propertyNodes.Add(propertyNode);
                }
                
                propertyNodes[(int)CityTaxFieldName.State].InnerText = citytaxinfo.State;
                propertyNodes[(int)CityTaxFieldName.LocalName].InnerText = citytaxinfo.LocalName;
                propertyNodes[(int)CityTaxFieldName.LocRate].InnerText = citytaxinfo.LocRate;
                propertyNodes[(int)CityTaxFieldName.SchlRate].InnerText = citytaxinfo.SchlRate;
                propertyNodes[(int)CityTaxFieldName.WhRate].InnerText = citytaxinfo.WhRate;
                propertyNodes[(int)CityTaxFieldName.W2Name].InnerText = citytaxinfo.W2Name;
                propertyNodes[(int)CityTaxFieldName.LocalCD].InnerText = citytaxinfo.LocalCD;
                propertyNodes[(int)CityTaxFieldName.ResInd].InnerText = citytaxinfo.ResInd;
                propertyNodes[(int)CityTaxFieldName.CountyName].InnerText = citytaxinfo.CountyName;
                propertyNodes[(int)CityTaxFieldName.AdditionalJur].InnerText = citytaxinfo.AdditionalJur;
                propertyNodes[(int)CityTaxFieldName.Comments].InnerText = citytaxinfo.Comments;

                foreach (XmlNode node in propertyNodes)
                {
                    cityTaxsNode.AppendChild(node);
                }
                root.AppendChild(cityTaxsNode);
            }
            xml.AppendChild(root);
            return xml.InnerXml;
        }

        public void ImportModule(int ModuleID, string Content, string Version, int UserID)
        {
            // create xml document to analysis the data
            XmlDocument xml = new XmlDocument();
            xml.LoadXml(Content);
            XmlNodeList xmlCityTaxs = xml.GetElementsByTagName(CityTaxInfoConstant.XmlNodeName);

            // analysis data in xml, and convert it to CityTaxInfo list
            List<CityTaxInfo> cityTaxInfos = new List<CityTaxInfo>();
            foreach (XmlNode xmlCityTax in xmlCityTaxs)
            {
                CityTaxInfo objCityTax = new CityTaxInfo();

                objCityTax.State = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.State).InnerText;
                objCityTax.LocalName = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.LocalName).InnerText;
                objCityTax.LocRate = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.LocRate).InnerText;
                objCityTax.SchlRate = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.SchlRate).InnerText;
                objCityTax.WhRate = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.WhRate).InnerText;
                objCityTax.W2Name = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.W2Name).InnerText;
                objCityTax.LocalCD = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.LocalCD).InnerText;
                objCityTax.ResInd = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.ResInd).InnerText;
                objCityTax.CountyName = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.CountyName).InnerText;
                objCityTax.AdditionalJur = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.AdditionalJur).InnerText;
                objCityTax.Comments = xmlCityTax.ChildNodes.Item((int)CityTaxFieldName.Comments).InnerText;

                cityTaxInfos.Add(objCityTax);
            }

            // insert data to data base
            InsertCityTaxInfo(cityTaxInfos);
        }

        #endregion

        #region ISearchable Members

        SearchItemInfoCollection ISearchable.GetSearchItems(ModuleInfo ModInfo)
        {
            SearchItemInfoCollection searchItems = new SearchItemInfoCollection();

            // get all items for search
            List<CityTaxInfo> cityTaxs = new List<CityTaxInfo>();
            cityTaxs = GetALLCityTax();

            foreach (CityTaxInfo cityTaxInfo in cityTaxs)
            {
                SearchItemInfo item = new SearchItemInfo();

                // create title for search
                string title = ModInfo.ModuleTitle + "-" + cityTaxInfo.LocalName + "," + cityTaxInfo.State;

                // create content for search
                StringBuilder content = new StringBuilder();
                content.Append(cityTaxInfo.AdditionalJur);
                content.Append(" ");
                content.Append(cityTaxInfo.Comments);
                content.Append(" ");
                content.Append(cityTaxInfo.CountyName);
                content.Append(" ");
                content.Append(cityTaxInfo.LocalName);
                content.Append(" ");
                content.Append(cityTaxInfo.State);
                content.Append(" ");
                content.Append(cityTaxInfo.W2Name);

                // create GUID
                // "ctl=ViewDetal" is the control name,
                // "mid=" is the ModuleID
                // "CityTaxID=" is the ID the control needed
                string guid = "ctl=ViewDetal&mid=" + ModInfo.ModuleID.ToString() + "&CityTaxID=" + cityTaxInfo.CityTaxID.ToString();

                item = new SearchItemInfo(title, "", 1, DateTime.Now, ModInfo.ModuleID, cityTaxInfo.CityTaxID.ToString(), content.ToString(), guid);

                searchItems.Add(item);
            }

            return searchItems;
        }
        #endregion
    }
}